Merging Data Frames

Let's learn how to merge Data Frames together (you'll use this in your Final Data Frame Project!)

## use character columns of names to get sensible sort order
authors <- data.frame(
    surname = I(c("Tukey", "Venables", "Tierney", "Ripley", "McNeil")),
    nationality = c("US", "Australia", "US", "UK", "Australia"),
    deceased = c("yes", rep("no", 4)))

books <- data.frame(
    name = I(c("Tukey", "Venables", "Tierney",
             "Ripley", "Ripley", "McNeil", "R Core")),
    title = c("Exploratory Data Analysis",
              "Modern Applied Statistics ...",
              "LISP-STAT",
              "Spatial Statistics", "Stochastic Simulation",
              "Interactive Data Analysis",
              "An Introduction to R"),
    other.author = c(NA, "Ripley", NA, NA, NA, NA,
                     "Venables & Smith"))

authors

	surname	nationality	deceased
1	Tukey	US	yes
2	Venables	Australia	no
3	Tierney	US	no
4	Ripley	UK	no
5	McNeil	Australia	no

(m1 <- merge(authors, books, by.x = "surname", by.y = "name"))

(m2 <- merge(books, authors, by.x = "name", by.y = "surname"))
stopifnot(as.character(m1[, 1]) == as.character(m2[, 1]),
          all.equal(m1[, -1], m2[, -1][ names(m1)[-1] ]),
          dim(merge(m1, m2, by = integer(0))) == c(36, 10))

## "R core" is missing from authors and appears only here :
merge(authors, books, by.x = "surname", by.y = "name", all = TRUE)

## example of using 'incomparables'
x <- data.frame(k1 = c(NA,NA,3,4,5), k2 = c(1,NA,NA,4,5), data = 1:5)
y <- data.frame(k1 = c(NA,2,NA,4,5), k2 = c(NA,NA,3,4,5), data = 1:5)
merge(x, y, by = c("k1","k2")) # NA's match
merge(x, y, by = "k1") # NA's match, so 6 rows
merge(x, y, by = "k2", incomparables = NA) # 2 rows

	surname	nationality	deceased	title	other.author
1	McNeil	Australia	no	Interactive Data Analysis	NA
2	Ripley	UK	no	Spatial Statistics	NA
3	Ripley	UK	no	Stochastic Simulation	NA
4	Tierney	US	no	LISP-STAT	NA
5	Tukey	US	yes	Exploratory Data Analysis	NA
6	Venables	Australia	no	Modern Applied Statistics ...	Ripley

	name	title	other.author	nationality	deceased
1	McNeil	Interactive Data Analysis	NA	Australia	no
2	Ripley	Spatial Statistics	NA	UK	no
3	Ripley	Stochastic Simulation	NA	UK	no
4	Tierney	LISP-STAT	NA	US	no
5	Tukey	Exploratory Data Analysis	NA	US	yes
6	Venables	Modern Applied Statistics ...	Ripley	Australia	no

	surname	nationality	deceased	title	other.author
1	McNeil	Australia	no	Interactive Data Analysis	NA
2	R Core	NA	NA	An Introduction to R	Venables & Smith
3	Ripley	UK	no	Spatial Statistics	NA
4	Ripley	UK	no	Stochastic Simulation	NA
5	Tierney	US	no	LISP-STAT	NA
6	Tukey	US	yes	Exploratory Data Analysis	NA
7	Venables	Australia	no	Modern Applied Statistics ...	Ripley

	k1	k2	data.x	data.y
1	4	4	4	4
2	5	5	5	5
3	NA	NA	2	1

	k1	k2.x	data.x	k2.y	data.y
1	4	4	4	4	4
2	5	5	5	5	5
3	NA	1	1	NA	1
4	NA	1	1	3	3
5	NA	NA	2	NA	1
6	NA	NA	2	3	3

	k2	k1.x	data.x	k1.y	data.y
1	4	4	4	4	4
2	5	5	5	5	5